﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[usp_DespieceHijosPartesObtener]
	@idProducto int,
	@idParte int
AS
BEGIN
	DECLARE @CurrentDespiece hierarchyid

	SELECT @CurrentDespiece = Jerarquia
	FROM Despiece
	--WHERE IdProducto = @idProducto and Nivel=1
	WHERE IdProducto = @idProducto and IdParte=@idParte

	SELECT IdProducto, 
			D.IdParte, 
			P.Descripcion, 
			Jerarquia.ToString() as Jerarquia, 
			Nivel, 
			Cantidad,
			(select idparte from Despiece where Jerarquia=d.Jerarquia.GetAncestor(1) ) as IdPartePadre
	FROM Despiece D
	Inner join Parte P on D.IdParte = P.IdParte
	WHERE Jerarquia.IsDescendantOf(@CurrentDespiece) = 1
	order by  Jerarquia desc
END